Relationships between tables

In Microsoft Access, a RELATIONSHIP is an association between common fields in two tables. There can be one-to-one, one-to-many, and many-to-many relationships.

In a ONE-TO-ONE relationship, for each item of data in one table, there is one item of related information in another table. Each record in the primary table matches exactly one record in the related table.

In a ONE-TO-MANY relationship, for every one item of data in one table, there may be many items of related information in another table. Each record in the primary table matches many records in the related table, but each record in the related table relates to only one record in the primary table.

In a MANY-TO-MANY relationship, one record in EITHER table can have more than one matching record in the other table. Usually, a third table is needed to link the two tables before a relationship can be created.

Relationships are defined by using the Relationships window in Microsoft Access.

NOTE: Each foreign key (a column in a table that references a primary key in a related table) and its related primary key (a unique field that cannot have duplicate values) must share a common meaning and get their values from the same domain.